import math
import os
import time
import sys
from itertools import groupby
from operator import itemgetter

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)

from src.db.FCDB import FCDB
from src.db.ProdDb import ProdDb
from src.utils.CommonUtils import cleanScreen
from src.utils.LogUtils import Logger


class SyncBankFinData:
    """
    智慧金融-商业银行 数据导入
    """

    def __init__(self, logger, localDb, fcdb):
        self.logger = logger
        self.localDb = localDb
        self.fcdb = fcdb

    def __loadLocalData__(self):
        sql = """
            SELECT * FROM bank_fin_data 
        """
        return self.localDb.Query(sql)

    def __loadFinProBankCompare__(self):
        sql = """
           SELECT ID,COMPCODE,REPORTYEAR,REPORTDATETYPE,
            BADLOANRTO,BADLOAN,TDEP,TLOAN,NPLPCRT,NIS,
            NIM,LOANTODEPOSRT,LSILCRTO,NESTFURATIO,CAR,
            T1CAR,CORET1CAR,SINGBIGLOANRT,MAXTENLOANRT
           FROM tq_fin_probankcompare
           WHERE ISVALID=1 AND ACCSTACODE = '11002'
        """
        return self.fcdb.Query(sql)

    def __loadFinFnFcLoanc__(self):
        sql = """
            SELECT COMPCODE, REPORTDATE, ITEMCODE,AMOUNT
            FROM tq_fin_fnfcloanc
            WHERE ISVALID=1
        """
        return self.fcdb.Query(sql)

    def __update__(self, updateDatas):
        sql = """
            REPLACE INTO bank_fin_data
            (ID,COMPCODE,REPORTYEAR,REPORTDATETYPE,
            BADLOANRTO,BADLOAN,TDEP,TLOAN,NPLPCRT,NIS,
            NIM,LOANTODEPOSRT,LSILCRTO,NESTFURATIO,CAR,
            T1CAR,CORET1CAR,SINGBIGLOANRT,MAXTENLOANRT,
            AMOUNT1,AMOUNT2,AMOUNT3,AMOUNT4,AMOUNT5)
            VALUES(%(ID)s,%(COMPCODE)s,%(REPORTYEAR)s,%(REPORTDATETYPE)s
            ,%(BADLOANRTO)s,%(BADLOAN)s,%(TDEP)s,%(TLOAN)s,%(NPLPCRT)s,%(NIS)s,
            %(NIM)s,%(LOANTODEPOSRT)s,%(LSILCRTO)s,%(NESTFURATIO)s,%(CAR)s,
            %(T1CAR)s,%(CORET1CAR)s,%(SINGBIGLOANRT)s,%(MAXTENLOANRT)s,
            %(AMOUNT1)s,%(AMOUNT2)s,%(AMOUNT3)s,%(AMOUNT4)s,%(AMOUNT5)s);
        """
        return self.localDb.ExecMany(sql, updateDatas)

    def sync(self):

        self.logger.info("加载本地数据")
        localDatas = self.__loadLocalData__()
        localDatas = {self.__genKey__(d): d for d in localDatas}
        self.logger.info(f"加载本地数据 {len(localDatas)}")

        self.logger.info("加载银行专项对比")
        finProBankCompareDatas = self.__loadFinProBankCompare__()
        self.logger.info(f"加载银行专项对比 {len(finProBankCompareDatas)}")

        updatedCount = 0
        insertedCount = 0
        for finProBankCompareData in finProBankCompareDatas:
            key = self.__genKey__(finProBankCompareData)
            if key in localDatas.keys():
                data = localDatas[key]
                self.__mergeDict__(data, finProBankCompareData)
                updatedCount += 1
            else:
                data = self.__genEmptyData__()
                self.__mergeDict__(data, finProBankCompareData)
                localDatas[key] = data
                insertedCount += 1

        self.logger.info(f"银行专项对比 新增：{insertedCount},更新：{updatedCount}")

        self.logger.info("加载财务附注_贷款五级分类")
        finFnFcLoancDatas = self.__loadFinFnFcLoanc__()
        self.logger.info(f"加载财务附注_贷款五级分类 {len(finFnFcLoancDatas)}")
        updatedCount = 0
        insertedCount = 0
        for finFnFcLoancData in finFnFcLoancDatas:
            reportYear, reportDateType = self.__convertReportDate__(finFnFcLoancData["REPORTDATE"])
            key = (finFnFcLoancData["COMPCODE"], reportYear, reportDateType)
            if key in localDatas.keys():
                data = localDatas[key]
                data["AMOUNT" + finFnFcLoancData["ITEMCODE"]] = finFnFcLoancData["AMOUNT"]
                updatedCount += 1
            else:
                data = self.__genEmptyData__()
                data["COMPCODE"] = finFnFcLoancData["COMPCODE"]
                data["REPORTYEAR"] = reportYear
                data["REPORTDATETYPE"] = reportDateType
                data["AMOUNT" + finFnFcLoancData["ITEMCODE"]] = finFnFcLoancData["AMOUNT"]
                localDatas[key] = data
                insertedCount += 1
        self.logger.info(f"财务附注_贷款五级分类 新增：{insertedCount},更新：{updatedCount}")

        # 写库
        updateDatas = localDatas.values()
        self.__update__(updateDatas)
        self.logger.info(f"本地数据更新 更新：{len(updateDatas)}")

    @staticmethod
    def __mergeDict__(dest: dict, src: dict):
        for key in src.keys():
            if key.startswith("__"):
                continue
            if src[key] is not None:
                dest[key] = src[key]

    @staticmethod
    def __genKey__(data: dict):
        return data["COMPCODE"], data["REPORTYEAR"], data["REPORTDATETYPE"]

    @staticmethod
    def __convertReportDate__(reportDate):
        reportYear = None
        reportDateType = None
        if reportDate is None:
            return reportYear, reportDateType
        reportYear = reportDate[:4]
        md = reportDate[4:]
        if md == "0331":
            return reportYear, "1"
        if md == "0630":
            return reportYear, "2"
        if md == "0930":
            return reportYear, "3"
        if md == "1231":
            return reportYear, "4"

        return reportYear, reportDateType

    # 空数据
    @staticmethod
    def __genEmptyData__():
        return {
            "ID": None,
            "COMPCODE": None,
            "REPORTYEAR": None,
            "REPORTDATETYPE": None,
            "BADLOANRTO": None,
            "BADLOAN": None,
            "TDEP": None,
            "TLOAN": None,
            "NPLPCRT": None,
            "NIS": None,
            "NIM": None,
            "LOANTODEPOSRT": None,
            "LSILCRTO": None,
            "NESTFURATIO": None,
            "CAR": None,
            "T1CAR": None,
            "CORET1CAR": None,
            "SINGBIGLOANRT": None,
            "MAXTENLOANRT": None,
            "AMOUNT1": None,
            "AMOUNT2": None,
            "AMOUNT3": None,
            "AMOUNT4": None,
            "AMOUNT5": None
        }

    @staticmethod
    def __getDictData__(datas: dict, key):
        if datas is None or key is None:
            return None
        if key in datas.keys():
            return datas[key]
        return None


# 主函数
def main():
    logger = Logger()
    cleanScreen()

    localDb = ProdDb(logSql=False, autocommit=True)
    fcdb = FCDB(logSql=False, autocommit=True)

    t = time.perf_counter()

    logger.info("========同步智慧金融-商业银行 开始 ========")

    sync = SyncBankFinData(logger=logger, localDb=localDb, fcdb=fcdb)
    sync.sync()

    cast = (time.perf_counter() - t)
    m = math.floor(cast / 60)
    s = cast - m * 60
    logger.info('总耗时: %d分%.3f秒' % (m, s))
    logger.info("========同步智慧金融-商业银行 结束 ========")


# 程序入口
if __name__ == "__main__":
    main()
